package com.struts2.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;


/**
 * PreparedStatement 封装类
 * @author Administrator
 * @date 2017年7月21日 下午4:36:14
 * @version v1.0
 */
public class DBUtilPreparedStatement {
	
	private final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
	private final String DB_URL = "jdbc:mysql://127.0.0.1:3306/studentsys";
	private final String USER = "root";
	private final String PASS = "123456";
	
	private Connection conn = null;
	private PreparedStatement state = null;
	private ResultSet result = null;
	/**
	 * 查询
	 * @author Administrator
	 * @date  2017年7月21日 下午5:18:23
	 * @version v1.0
	 * @param sql
	 * @param prepar
	 * @return
	 */
	public List<Object[]> select(String sql, Object[] prepar){
		List<Object[]> list = new ArrayList<Object[]>();
		try {
			conn = getConnection();
			state = conn.prepareStatement(sql);
			if(prepar!=null && prepar.length>0 ){
				for (int i = 0,len=prepar.length; i < len; i++) {
					state.setObject(i+1,prepar[i]);
				}
			}
			result = state.executeQuery();
			
			ResultSetMetaData metaData = result.getMetaData();
			int columnCount = metaData.getColumnCount();
			
			while(result.next()){
				Object[] object = new Object[columnCount];
				for (int i = 0; i < columnCount; i++) {
					object[i]=result.getObject(i+1);
				}
				list.add(object);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			close();
		}
		return list;
	}
	/**
	 * 增、删、改
	 * @author Administrator
	 * @date  2017年7月21日 下午5:28:47
	 * @version v1.0
	 * @param sql
	 * @param prepar
	 * @return
	 */
	public int opertorIUD(String sql, Object[] prepar){
		int resultNum = 0;
		try {
			conn=getConnection();
			state = conn.prepareStatement(sql);
			if(prepar!=null && prepar.length>0){
				for (int i = 0,len=prepar.length; i < len; i++) {
					state.setObject(i+1, prepar[i]);
				}
			}
			resultNum = state.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return resultNum;
	}
	/**
	 * 创建连接
	 * @author Administrator
	 * @date  2017年7月21日 下午5:13:34
	 * @version v1.0
	 * @return
	 * @throws Exception
	 */
	public Connection getConnection() throws Exception {
			Class.forName(JDBC_DRIVER);
			return DriverManager.getConnection(DB_URL, USER, PASS);
		
	}
	/**
	 * 关闭
	 * @author Administrator
	 * @date  2017年7月21日 下午5:13:51
	 * @version v1.0
	 */
	public void close(){
		try {
			if(result!=null){
				result.close();
			}
			if(state!=null){
				state.close();
			}
			if(conn!=null){
				conn.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public ResultSet selectToResultSet(String sql, Object[] prepar){
		try {
			conn = getConnection();
			state = conn.prepareStatement(sql);
			if(prepar!=null && prepar.length>0 ){
				for (int i = 0,len=prepar.length; i < len; i++) {
					state.setObject(i+1,prepar[i]);
				}
			}
			result = state.executeQuery();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	
	public ResultSet search(Page page,String sql,Object[] prepar){
		try {
			//查询符合条件的记录总条数
			conn = getConnection();
			String sqlCount = "select count(1) "+sql.substring(sql.indexOf(" from "));
			if(sqlCount.indexOf(" group by ")>0){
				sqlCount = sqlCount.substring(0,sqlCount.indexOf(" group by "));
			}
			state = conn.prepareStatement(sqlCount);
			if(prepar!=null && prepar.length>0){
				for (int i = 0,len=prepar.length; i <len; i++) {
					state.setObject(i+1, prepar[i]);
				}
			}
			result = state.executeQuery();
			result.next();
			page.setTotalNum(result.getInt(1));
			
			//对结果分页
			sql += " limit " +((page.getPageNo()-1)*page.getPageSize())+" , "+page.getPageSize();
			state = conn.prepareStatement(sql);
			if(prepar!=null && prepar.length>0){
				for (int i = 0,len=prepar.length; i <len; i++) {
					state.setObject(i+1, prepar[i]);
				}
			}
			return state.executeQuery();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
}
